{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 1\n",
    "\n",
    "Using method chaining, and without assigning the downloaded data to a variable, can you return the current value? Your solution should consist of a single line, which includes the download, selection, and calculation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "365    36497.35\n",
       "Name: value, dtype: float64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('https://api.blockchain.info/charts/market-price?format=csv',\n",
    "                header=None,\n",
    "                names=['date', 'value']).tail(1)['value']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 2\n",
    "\n",
    "* The `pd.read_html` function, like `pd.read_csv`, takes a file-like object or a URL. It assumes that it'll encounter HTML-formatted text containing at least one table. It turns each table into a data frame, then returns a list of those data frames. With this in mind, retrieve 1 year of historical S&P 500 data from Yahoo Finance (https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC), looking only at the `Date`, `Close`, and `Volume` columns. Show the date and volume of the days with the highest and lowest `Close` values. Note that Yahoo seems to look at the `User-Agent` header in the HTTP request, which cannot be set in `read_html`. So you'll need to use `requests` to retrieve the data, setting `User-Agent` to a string equal to `'Mozilla 5.0'`. Turn the content of the result into a `StringIO`, and then finally feed that to `read_html` and retrieve the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close*</th>\n",
       "      <th>Adj Close**</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Nov 13, 2023</th>\n",
       "      <td>4406.66</td>\n",
       "      <td>4421.76</td>\n",
       "      <td>4393.82</td>\n",
       "      <td>4411.55</td>\n",
       "      <td>4411.55</td>\n",
       "      <td>3326240000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nov 10, 2023</th>\n",
       "      <td>4364.15</td>\n",
       "      <td>4418.03</td>\n",
       "      <td>4353.34</td>\n",
       "      <td>4415.24</td>\n",
       "      <td>4415.24</td>\n",
       "      <td>3665080000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nov 09, 2023</th>\n",
       "      <td>4391.41</td>\n",
       "      <td>4393.40</td>\n",
       "      <td>4343.94</td>\n",
       "      <td>4347.35</td>\n",
       "      <td>4347.35</td>\n",
       "      <td>3900780000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nov 08, 2023</th>\n",
       "      <td>4384.37</td>\n",
       "      <td>4391.20</td>\n",
       "      <td>4359.76</td>\n",
       "      <td>4382.78</td>\n",
       "      <td>4382.78</td>\n",
       "      <td>3729510000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nov 07, 2023</th>\n",
       "      <td>4366.21</td>\n",
       "      <td>4386.26</td>\n",
       "      <td>4355.41</td>\n",
       "      <td>4378.38</td>\n",
       "      <td>4378.38</td>\n",
       "      <td>3791230000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 29, 2023</th>\n",
       "      <td>4374.94</td>\n",
       "      <td>4398.39</td>\n",
       "      <td>4371.97</td>\n",
       "      <td>4396.44</td>\n",
       "      <td>4396.44</td>\n",
       "      <td>3696660000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 28, 2023</th>\n",
       "      <td>4367.48</td>\n",
       "      <td>4390.35</td>\n",
       "      <td>4360.22</td>\n",
       "      <td>4376.86</td>\n",
       "      <td>4376.86</td>\n",
       "      <td>3739330000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 27, 2023</th>\n",
       "      <td>4337.36</td>\n",
       "      <td>4384.42</td>\n",
       "      <td>4335.00</td>\n",
       "      <td>4378.41</td>\n",
       "      <td>4378.41</td>\n",
       "      <td>3573500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 26, 2023</th>\n",
       "      <td>4344.84</td>\n",
       "      <td>4362.06</td>\n",
       "      <td>4328.08</td>\n",
       "      <td>4328.82</td>\n",
       "      <td>4328.82</td>\n",
       "      <td>3415030000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 23, 2023</th>\n",
       "      <td>4354.17</td>\n",
       "      <td>4366.55</td>\n",
       "      <td>4341.34</td>\n",
       "      <td>4348.33</td>\n",
       "      <td>4348.33</td>\n",
       "      <td>6053620000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Open     High      Low   Close* Adj Close**      Volume\n",
       "Date                                                                    \n",
       "Nov 13, 2023  4406.66  4421.76  4393.82  4411.55     4411.55  3326240000\n",
       "Nov 10, 2023  4364.15  4418.03  4353.34  4415.24     4415.24  3665080000\n",
       "Nov 09, 2023  4391.41  4393.40  4343.94  4347.35     4347.35  3900780000\n",
       "Nov 08, 2023  4384.37  4391.20  4359.76  4382.78     4382.78  3729510000\n",
       "Nov 07, 2023  4366.21  4386.26  4355.41  4378.38     4378.38  3791230000\n",
       "...               ...      ...      ...      ...         ...         ...\n",
       "Jun 29, 2023  4374.94  4398.39  4371.97  4396.44     4396.44  3696660000\n",
       "Jun 28, 2023  4367.48  4390.35  4360.22  4376.86     4376.86  3739330000\n",
       "Jun 27, 2023  4337.36  4384.42  4335.00  4378.41     4378.41  3573500000\n",
       "Jun 26, 2023  4344.84  4362.06  4328.08  4328.82     4328.82  3415030000\n",
       "Jun 23, 2023  4354.17  4366.55  4341.34  4348.33     4348.33  6053620000\n",
       "\n",
       "[100 rows x 6 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "from io import StringIO\n",
    "\n",
    "r = requests.get('https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC', \n",
    "                headers={'User-Agent': 'Mozilla/5.0'})\n",
    "\n",
    "df = pd.read_html(StringIO(r.content.decode()))[0].set_index('Date').iloc[:-1]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close*</th>\n",
       "      <th>Adj Close**</th>\n",
       "      <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Nov 13, 2023</th>\n",
       "      <td>4406.66</td>\n",
       "      <td>4421.76</td>\n",
       "      <td>4393.82</td>\n",
       "      <td>4411.55</td>\n",
       "      <td>4411.55</td>\n",
       "      <td>3326240000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nov 10, 2023</th>\n",
       "      <td>4364.15</td>\n",
       "      <td>4418.03</td>\n",
       "      <td>4353.34</td>\n",
       "      <td>4415.24</td>\n",
       "      <td>4415.24</td>\n",
       "      <td>3665080000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nov 09, 2023</th>\n",
       "      <td>4391.41</td>\n",
       "      <td>4393.40</td>\n",
       "      <td>4343.94</td>\n",
       "      <td>4347.35</td>\n",
       "      <td>4347.35</td>\n",
       "      <td>3900780000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nov 08, 2023</th>\n",
       "      <td>4384.37</td>\n",
       "      <td>4391.20</td>\n",
       "      <td>4359.76</td>\n",
       "      <td>4382.78</td>\n",
       "      <td>4382.78</td>\n",
       "      <td>3729510000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nov 07, 2023</th>\n",
       "      <td>4366.21</td>\n",
       "      <td>4386.26</td>\n",
       "      <td>4355.41</td>\n",
       "      <td>4378.38</td>\n",
       "      <td>4378.38</td>\n",
       "      <td>3791230000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 29, 2023</th>\n",
       "      <td>4374.94</td>\n",
       "      <td>4398.39</td>\n",
       "      <td>4371.97</td>\n",
       "      <td>4396.44</td>\n",
       "      <td>4396.44</td>\n",
       "      <td>3696660000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 28, 2023</th>\n",
       "      <td>4367.48</td>\n",
       "      <td>4390.35</td>\n",
       "      <td>4360.22</td>\n",
       "      <td>4376.86</td>\n",
       "      <td>4376.86</td>\n",
       "      <td>3739330000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 27, 2023</th>\n",
       "      <td>4337.36</td>\n",
       "      <td>4384.42</td>\n",
       "      <td>4335.00</td>\n",
       "      <td>4378.41</td>\n",
       "      <td>4378.41</td>\n",
       "      <td>3573500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 26, 2023</th>\n",
       "      <td>4344.84</td>\n",
       "      <td>4362.06</td>\n",
       "      <td>4328.08</td>\n",
       "      <td>4328.82</td>\n",
       "      <td>4328.82</td>\n",
       "      <td>3415030000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 23, 2023</th>\n",
       "      <td>4354.17</td>\n",
       "      <td>4366.55</td>\n",
       "      <td>4341.34</td>\n",
       "      <td>4348.33</td>\n",
       "      <td>4348.33</td>\n",
       "      <td>6053620000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Open     High      Low   Close* Adj Close**      Volume\n",
       "Date                                                                    \n",
       "Nov 13, 2023  4406.66  4421.76  4393.82  4411.55     4411.55  3326240000\n",
       "Nov 10, 2023  4364.15  4418.03  4353.34  4415.24     4415.24  3665080000\n",
       "Nov 09, 2023  4391.41  4393.40  4343.94  4347.35     4347.35  3900780000\n",
       "Nov 08, 2023  4384.37  4391.20  4359.76  4382.78     4382.78  3729510000\n",
       "Nov 07, 2023  4366.21  4386.26  4355.41  4378.38     4378.38  3791230000\n",
       "...               ...      ...      ...      ...         ...         ...\n",
       "Jun 29, 2023  4374.94  4398.39  4371.97  4396.44     4396.44  3696660000\n",
       "Jun 28, 2023  4367.48  4390.35  4360.22  4376.86     4376.86  3739330000\n",
       "Jun 27, 2023  4337.36  4384.42  4335.00  4378.41     4378.41  3573500000\n",
       "Jun 26, 2023  4344.84  4362.06  4328.08  4328.82     4328.82  3415030000\n",
       "Jun 23, 2023  4354.17  4366.55  4341.34  4348.33     4348.33  6053620000\n",
       "\n",
       "[100 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Close*'] = df['Close*'].astype(np.float64)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 3\n",
    "\n",
    "Create a two-row data frame with the highest and lowest closing prices for the S&P 500. Use the `to_csv` function to write this data to a new CSV file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date,Close*\n",
      "\"Oct 27, 2023\",4117.37\n",
      "\"Jul 31, 2023\",4588.96\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(df.loc[df['Close*'].agg(['idxmin', 'idxmax']), 'Close*'].to_csv())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
